using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Linq;

namespace MovieDB
{
    /// <summary>
    /// 电影数据库管理器
    /// 提供对电影数据库的CRUD操作
    /// 支持MySQL和SQL Server数据库
    /// </summary>
    public class MovieDBManager : IDisposable
    {
        private string _connectionString;
        private DbConnection _connection;
        private bool _useMysql;
        private bool _disposed = false;
        
        /// <summary>
        /// 构造函数
        /// </summary>
        /// <param name="connectionString">数据库连接字符串</param>
        /// <param name="useMysql">是否使用MySQL数据库（默认为true）</param>
        public MovieDBManager(string connectionString, bool useMysql = true)
        {
            _connectionString = connectionString;
            _useMysql = useMysql;
            _connection = CreateConnection();
        }
        
        /// <summary>
        /// 创建数据库连接
        /// </summary>
        /// <returns>数据库连接对象</returns>
        private DbConnection CreateConnection()
        {
            if (_useMysql)
            {
                // MySQL连接
                // 注意：使用MySQL需要添加MySql.Data引用
                // 这里为了兼容性，使用了DbConnection接口
                // 实际使用时需要根据数据库类型选择合适的驱动
                throw new NotImplementedException("MySQL连接需要MySql.Data引用，请根据实际情况修改代码");
            }
            else
            {
                // SQL Server连接
                return new System.Data.SqlClient.SqlConnection(_connectionString);
            }
        }
        
        /// <summary>
        /// 获取默认的SQL Server连接字符串
        /// </summary>
        /// <param name="server">服务器名称</param>
        /// <param name="database">数据库名称</param>
        /// <param name="integratedSecurity">是否使用Windows身份验证</param>
        /// <param name="userId">用户名（集成身份验证时忽略）</param>
        /// <param name="password">密码（集成身份验证时忽略）</param>
        /// <returns>数据库连接字符串</returns>
        public static string GetSqlServerConnectionString(string server = "localhost", string database = "MovieDB", bool integratedSecurity = true, string userId = "", string password = "")
        {
            System.Data.SqlClient.SqlConnectionStringBuilder builder = new System.Data.SqlClient.SqlConnectionStringBuilder
            {
                DataSource = server,
                InitialCatalog = database,
                IntegratedSecurity = integratedSecurity
            };
            
            if (!integratedSecurity)
            {
                builder.UserID = userId;
                builder.Password = password;
            }
            
            return builder.ConnectionString;
        }
        
        /// <summary>
        /// 获取默认的MySQL连接字符串
        /// </summary>
        /// <param name="server">服务器名称</param>
        /// <param name="database">数据库名称</param>
        /// <param name="userId">用户名</param>
        /// <param name="password">密码</param>
        /// <param name="port">端口号</param>
        /// <returns>数据库连接字符串</returns>
        public static string GetMySqlConnectionString(string server = "localhost", string database = "MovieDB", string userId = "root", string password = "", int port = 3306)
        {
            // MySQL连接字符串格式
            return $"server={server};port={port};database={database};user={userId};password={password};";
        }
        
        /// <summary>
        /// 打开数据库连接
        /// </summary>
        public void OpenConnection()
        {
            if (_connection.State != ConnectionState.Open)
            {
                _connection.Open();
            }
        }
        
        /// <summary>
        /// 关闭数据库连接
        /// </summary>
        public void CloseConnection()
        {
            if (_connection.State != ConnectionState.Closed)
            {
                _connection.Close();
            }
        }
        
        #region 艺人相关操作
        
        /// <summary>
        /// 添加艺人
        /// </summary>
        /// <param name="artist">艺人数组</param>
        /// <returns>添加成功的记录数</returns>
        public int AddArtist(Artist artist)
        {
            string sql = _useMysql ?
                "INSERT INTO Artist (name, birthday, sex, imdbnumber, introduction) VALUES (@name, @birthday, @sex, @imdbnumber, @introduction); SELECT LAST_INSERT_ID();" :
                "INSERT INTO Artist (name, birthday, sex, imdbnumber, introduction) VALUES (@name, @birthday, @sex, @imdbnumber, @introduction); SELECT SCOPE_IDENTITY();";
            
            using (DbCommand cmd = _connection.CreateCommand())
            {
                cmd.CommandText = sql;
                AddArtistParameters(cmd, artist);
                
                OpenConnection();
                object result = cmd.ExecuteScalar();
                artist.Id = Convert.ToInt32(result);
                return 1;
            }
        }
        
        /// <summary>
        /// 为艺人命令添加参数
        /// </summary>
        /// <param name="cmd">数据库命令对象</param>
        /// <param name="artist">艺人对象</param>
        private void AddArtistParameters(DbCommand cmd, Artist artist)
        {
            AddParameter(cmd, "@name", DbType.String, artist.Name);
            AddParameter(cmd, "@birthday", DbType.Date, artist.Birthday ?? (object)DBNull.Value);
            AddParameter(cmd, "@sex", DbType.String, artist.Sex.ToString());
            AddParameter(cmd, "@imdbnumber", DbType.String, artist.ImdbNumber ?? (object)DBNull.Value);
            AddParameter(cmd, "@introduction", DbType.String, artist.Introduction ?? (object)DBNull.Value);
        }
        
        /// <summary>
        /// 添加命令参数
        /// </summary>
        /// <param name="cmd">数据库命令对象</param>
        /// <param name="name">参数名</param>
        /// <param name="type">参数类型</param>
        /// <param name="value">参数值</param>
        private void AddParameter(DbCommand cmd, string name, DbType type, object value)
        {
            DbParameter param = cmd.CreateParameter();
            param.ParameterName = name;
            param.DbType = type;
            param.Value = value;
            cmd.Parameters.Add(param);
        }
        
        /// <summary>
        /// 获取所有艺人
        /// </summary>
        /// <returns>艺人数组</returns>
        public List<Artist> GetAllArtists()
        {
            List<Artist> artists = new List<Artist>();
            string sql = "SELECT id, name, birthday, sex, imdbnumber, introduction FROM Artist";
            
            using (DbCommand cmd = _connection.CreateCommand())
            {
                cmd.CommandText = sql;
                
                OpenConnection();
                using (DbDataReader reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        Artist artist = new Artist
                        {
                            Id = reader.GetInt32(0),
                            Name = reader.GetString(1),
                            Birthday = reader.IsDBNull(2) ? null : (DateTime?)reader.GetDateTime(2),
                            Sex = reader.GetString(3)[0],
                            ImdbNumber = reader.IsDBNull(4) ? null : reader.GetString(4),
                            Introduction = reader.IsDBNull(5) ? null : reader.GetString(5)
                        };
                        artists.Add(artist);
                    }
                }
            }
            
            return artists;
        }
        
        /// <summary>
        /// 根据ID获取艺人
        /// </summary>
        /// <param name="id">艺人ID</param>
        /// <returns>艺人对象</returns>
        public Artist GetArtistById(int id)
        {
            string sql = "SELECT id, name, birthday, sex, imdbnumber, introduction FROM Artist WHERE id = @id";
            
            using (DbCommand cmd = _connection.CreateCommand())
            {
                cmd.CommandText = sql;
                AddParameter(cmd, "@id", DbType.Int32, id);
                
                OpenConnection();
                using (DbDataReader reader = cmd.ExecuteReader())
                {
                    if (reader.Read())
                    {
                        return new Artist
                        {
                            Id = reader.GetInt32(0),
                            Name = reader.GetString(1),
                            Birthday = reader.IsDBNull(2) ? null : (DateTime?)reader.GetDateTime(2),
                            Sex = reader.GetString(3)[0],
                            ImdbNumber = reader.IsDBNull(4) ? null : reader.GetString(4),
                            Introduction = reader.IsDBNull(5) ? null : reader.GetString(5)
                        };
                    }
                }
            }
            
            return null;
        }
        
        /// <summary>
        /// 更新艺人信息
        /// </summary>
        /// <param name="artist">艺人对象</param>
        /// <returns>更新成功的记录数</returns>
        public int UpdateArtist(Artist artist)
        {
            string sql = "UPDATE Artist SET name = @name, birthday = @birthday, sex = @sex, imdbnumber = @imdbnumber, introduction = @introduction WHERE id = @id";
            
            using (DbCommand cmd = _connection.CreateCommand())
            {
                cmd.CommandText = sql;
                AddArtistParameters(cmd, artist);
                AddParameter(cmd, "@id", DbType.Int32, artist.Id);
                
                OpenConnection();
                return cmd.ExecuteNonQuery();
            }
        }
        
        /// <summary>
        /// 删除艺人
        /// </summary>
        /// <param name="id">艺人ID</param>
        /// <returns>删除成功的记录数</returns>
        public int DeleteArtist(int id)
        {
            // 使用事务确保数据一致性
            DbTransaction transaction = null;
            int result = 0;
            
            try
            {
                OpenConnection();
                transaction = _connection.BeginTransaction();
                
                // 先删除关联的参与关系
                string deleteParticipations = "DELETE FROM Participation WHERE artistid = @id";
                using (DbCommand cmd1 = _connection.CreateCommand())
                {
                    cmd1.CommandText = deleteParticipations;
                    cmd1.Transaction = transaction;
                    AddParameter(cmd1, "@id", DbType.Int32, id);
                    cmd1.ExecuteNonQuery();
                }
                
                // 再删除艺人
                string deleteArtist = "DELETE FROM Artist WHERE id = @id";
                using (DbCommand cmd2 = _connection.CreateCommand())
                {
                    cmd2.CommandText = deleteArtist;
                    cmd2.Transaction = transaction;
                    AddParameter(cmd2, "@id", DbType.Int32, id);
                    result = cmd2.ExecuteNonQuery();
                }
                
                transaction.Commit();
            }
            catch (Exception)
            {
                transaction?.Rollback();
                throw;
            }
            
            return result;
        }
        
        #endregion
        
        #region 电影相关操作
        
        /// <summary>
        /// 添加电影
        /// </summary>
        /// <param name="movie">电影对象</param>
        /// <returns>添加成功的记录数</returns>
        public int AddMovie(Movie movie)
        {
            string sql = _useMysql ?
                "INSERT INTO Movie (name, releasedate, duration, language, introduction, rating) VALUES (@name, @releasedate, @duration, @language, @introduction, @rating); SELECT LAST_INSERT_ID();" :
                "INSERT INTO Movie (name, releasedate, duration, language, introduction, rating) VALUES (@name, @releasedate, @duration, @language, @introduction, @rating); SELECT SCOPE_IDENTITY();";
            
            using (DbCommand cmd = _connection.CreateCommand())
            {
                cmd.CommandText = sql;
                AddMovieParameters(cmd, movie);
                
                OpenConnection();
                object result = cmd.ExecuteScalar();
                movie.Id = Convert.ToInt32(result);
                return 1;
            }
        }
        
        /// <summary>
        /// 为电影命令添加参数
        /// </summary>
        /// <param name="cmd">数据库命令对象</param>
        /// <param name="movie">电影对象</param>
        private void AddMovieParameters(DbCommand cmd, Movie movie)
        {
            AddParameter(cmd, "@name", DbType.String, movie.Name);
            AddParameter(cmd, "@releasedate", DbType.Date, movie.ReleaseDate ?? (object)DBNull.Value);
            AddParameter(cmd, "@duration", DbType.Int32, movie.Duration);
            AddParameter(cmd, "@language", DbType.String, movie.Language ?? (object)DBNull.Value);
            AddParameter(cmd, "@introduction", DbType.String, movie.Introduction ?? (object)DBNull.Value);
            AddParameter(cmd, "@rating", DbType.Single, movie.Rating ?? (object)DBNull.Value);
        }
        
        /// <summary>
        /// 获取所有电影
        /// </summary>
        /// <returns>电影数组</returns>
        public List<Movie> GetAllMovies()
        {
            List<Movie> movies = new List<Movie>();
            string sql = "SELECT id, name, releasedate, duration, language, introduction, rating FROM Movie";
            
            using (DbCommand cmd = _connection.CreateCommand())
            {
                cmd.CommandText = sql;
                
                OpenConnection();
                using (DbDataReader reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        Movie movie = new Movie
                        {
                            Id = reader.GetInt32(0),
                            Name = reader.GetString(1),
                            ReleaseDate = reader.IsDBNull(2) ? null : (DateTime?)reader.GetDateTime(2),
                            Duration = reader.GetInt32(3),
                            Language = reader.IsDBNull(4) ? null : reader.GetString(4),
                            Introduction = reader.IsDBNull(5) ? null : reader.GetString(5),
                            Rating = reader.IsDBNull(6) ? null : (float?)reader.GetFloat(6)
                        };
                        movies.Add(movie);
                    }
                }
            }
            
            return movies;
        }
        
        /// <summary>
        /// 根据ID获取电影
        /// </summary>
        /// <param name="id">电影ID</param>
        /// <returns>电影对象</returns>
        public Movie GetMovieById(int id)
        {
            string sql = "SELECT id, name, releasedate, duration, language, introduction, rating FROM Movie WHERE id = @id";
            
            using (DbCommand cmd = _connection.CreateCommand())
            {
                cmd.CommandText = sql;
                AddParameter(cmd, "@id", DbType.Int32, id);
                
                OpenConnection();
                using (DbDataReader reader = cmd.ExecuteReader())
                {
                    if (reader.Read())
                    {
                        return new Movie
                        {
                            Id = reader.GetInt32(0),
                            Name = reader.GetString(1),
                            ReleaseDate = reader.IsDBNull(2) ? null : (DateTime?)reader.GetDateTime(2),
                            Duration = reader.GetInt32(3),
                            Language = reader.IsDBNull(4) ? null : reader.GetString(4),
                            Introduction = reader.IsDBNull(5) ? null : reader.GetString(5),
                            Rating = reader.IsDBNull(6) ? null : (float?)reader.GetFloat(6)
                        };
                    }
                }
            }
            
            return null;
        }
        
        /// <summary>
        /// 更新电影信息
        /// </summary>
        /// <param name="movie">电影对象</param>
        /// <returns>更新成功的记录数</returns>
        public int UpdateMovie(Movie movie)
        {
            string sql = "UPDATE Movie SET name = @name, releasedate = @releasedate, duration = @duration, language = @language, introduction = @introduction, rating = @rating WHERE id = @id";

            using (DbCommand cmd = _connection.CreateCommand())
            {
                cmd.CommandText = sql;
                AddMovieParameters(cmd, movie);
                AddParameter(cmd, "@id", DbType.Int32, movie.Id);

                OpenConnection();
                return cmd.ExecuteNonQuery();
            }
        }

        #endregion

        #region 参与关系相关操作
        
        /// <summary>
        /// 添加艺人参与电影关系
        /// </summary>
        /// <param name="participation">参与关系对象</param>
        /// <returns>添加成功的记录数</returns>
        public int AddParticipation(Participation participation)
        {
            string sql = _useMysql ?
                "INSERT INTO Participation (artistid, movieid, rolename) VALUES (@artistid, @movieid, @rolename); SELECT LAST_INSERT_ID();" :
                "INSERT INTO Participation (artistid, movieid, rolename) VALUES (@artistid, @movieid, @rolename); SELECT SCOPE_IDENTITY();";
            
            using (DbCommand cmd = _connection.CreateCommand())
            {
                cmd.CommandText = sql;
                AddParticipationParameters(cmd, participation);
                
                OpenConnection();
                object result = cmd.ExecuteScalar();
                participation.Id = Convert.ToInt32(result);
                return 1;
            }
        }
        
        /// <summary>
        /// 为参与关系命令添加参数
        /// </summary>
        /// <param name="cmd">数据库命令对象</param>
        /// <param name="participation">参与关系对象</param>
        private void AddParticipationParameters(DbCommand cmd, Participation participation)
        {
            AddParameter(cmd, "@artistid", DbType.Int32, participation.ArtistId);
            AddParameter(cmd, "@movieid", DbType.Int32, participation.MovieId);
            AddParameter(cmd, "@rolename", DbType.String, participation.RoleName ?? (object)DBNull.Value);
        }
        
        /// <summary>
        /// 获取电影的所有演员
        /// </summary>
        /// <param name="movieId">电影ID</param>
        /// <returns>演员列表</returns>
        public List<Artist> GetArtistsByMovie(int movieId)
        {
            List<Artist> artists = new List<Artist>();
            string sql = @"SELECT a.id, a.name, a.birthday, a.sex, a.imdbnumber, a.introduction 
                           FROM Artist a 
                           JOIN Participation p ON a.id = p.artistid 
                           WHERE p.movieid = @movieid";
            
            using (DbCommand cmd = _connection.CreateCommand())
            {
                cmd.CommandText = sql;
                AddParameter(cmd, "@movieid", DbType.Int32, movieId);
                
                OpenConnection();
                using (DbDataReader reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        Artist artist = new Artist
                        {
                            Id = reader.GetInt32(0),
                            Name = reader.GetString(1),
                            Birthday = reader.IsDBNull(2) ? null : (DateTime?)reader.GetDateTime(2),
                            Sex = reader.GetString(3)[0],
                            ImdbNumber = reader.IsDBNull(4) ? null : reader.GetString(4),
                            Introduction = reader.IsDBNull(5) ? null : reader.GetString(5)
                        };
                        artists.Add(artist);
                    }
                }
            }
            
            return artists;
        }
        
        /// <summary>
        /// 获取艺人参演的所有电影
        /// </summary>
        /// <param name="artistId">艺人ID</param>
        /// <returns>电影列表</returns>
        public List<Movie> GetMoviesByArtist(int artistId)
        {
            List<Movie> movies = new List<Movie>();
            string sql = @"SELECT m.id, m.name, m.releasedate, m.duration, m.language, m.introduction, m.rating 
                           FROM Movie m 
                           JOIN Participation p ON m.id = p.movieid 
                           WHERE p.artistid = @artistid";
            
            using (DbCommand cmd = _connection.CreateCommand())
            {
                cmd.CommandText = sql;
                AddParameter(cmd, "@artistid", DbType.Int32, artistId);
                
                OpenConnection();
                using (DbDataReader reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        Movie movie = new Movie
                        {
                            Id = reader.GetInt32(0),
                            Name = reader.GetString(1),
                            ReleaseDate = reader.IsDBNull(2) ? null : (DateTime?)reader.GetDateTime(2),
                            Duration = reader.GetInt32(3),
                            Language = reader.IsDBNull(4) ? null : reader.GetString(4),
                            Introduction = reader.IsDBNull(5) ? null : reader.GetString(5),
                            Rating = reader.IsDBNull(6) ? null : (float?)reader.GetFloat(6)
                        };
                        movies.Add(movie);
                    }
                }
            }
            
            return movies;
        }
        
        #endregion
        
        #region IDisposable 实现
        
        /// <summary>
        /// 释放资源
        /// </summary>
        public void Dispose()
        {
            Dispose(true);
            GC.SuppressFinalize(this);
        }
        
        protected virtual void Dispose(bool disposing)
        {
            if (!_disposed)
            {
                if (disposing)
                {
                    // 释放托管资源
                    if (_connection != null)
                    {
                        _connection.Dispose();
                        _connection = null;
                    }
                }
                // 释放非托管资源
                _disposed = true;
            }
        }
        
        ~MovieDBManager()
        {
            Dispose(false);
        }
        
        #endregion
    }
}